<?php

/* 
 * By: Lloyd Sanchez
 */
 
class ExcelUtility{
	private $filename;
	
	public function __construct($filename){
		$this->filename	=	$filename;
	}
	
	public function ExportExcel(){
		//write file on drive
		//try to export 
		//if not ; return link to file
		
		/*$filename ="excelreport.xls";
		$contents = "testdata1 \t testdata2 \t testdata3 \t \n";
		header('Content-type: application/ms-excel');
		header('Content-Disposition: attachment; filename='.$filename);
		echo $contents;*/
	}
	
	public function ImportExcel(ExcelFormat $excelFormat,ExcelSaverBase $excelSaver){
		//importing done here
		$data	=	$this->_extractFromExcel($excelFormat);
		$excelSaver->setData($data);
		$excelSaver->setFormat($excelFormat);
		$excelSaver->save();
	}
	
	//read all from sheets->rows->columns..everything
	private function _readWholeFile(){
		Zend_Loader::loadClass("ExcelReader");	
		$reader		=	new ExcelReader();
		
		try{									//try reading if possible
			$reader->read($this->filename);
		}
		catch (Exception $e){
			echo 'Caught exception: ',  $e->getMessage(), "\n";
		}
		
		$rows		=	$reader->getTotalRows();
		$columns	=	$reader->getTotalColumns();
		$sheets		=	$reader->getTotalSheets();
		
		$result		=	Array();
		//print_r($rows.' - '.$columns.' - '.$sheets);die();
		for($scnt=0;$scnt<$sheets;$scnt++){
			
			for($rcnt=1;$rcnt<=$rows;$rcnt++){
			
				for($ccnt=1;$ccnt<=$columns;$ccnt++){
					$result[$scnt][$rcnt][$ccnt]		=	$reader->getValue($rcnt,$ccnt,$scnt);
				}
			}
		}
		return $result;
	}
	
	//Extract using the given ExcelFormat
	private function _extractFromExcel(ExcelFormat $excelFormat){
		$sheets			=	$this->_readWholeFile();
		$t_rows			=	$excelFormat->getRows();
		//print_r($sheets);die();
		//print_r($t_rows);die();
		
		$result			=	Array();
		for($scnt=0;$scnt<count($sheets);$scnt++){
			$rows		=	$sheets[$scnt];
			
			for($rcnt=1;$rcnt<=count($rows);$rcnt++){
				$columns=	$rows[$rcnt];
				
				for($ccnt=1;$ccnt<=count($columns);$ccnt++){
				
					//check if column,row,sheet is valid as suggested from the template
					foreach($t_rows as $format_row){
						if($format_row->column==$ccnt AND
							($format_row->row==$rcnt OR $format_row->row==0) AND
							($format_row->sheet==$scnt)){
							$result[$scnt][$rcnt][$format_row->name]	=	$columns[$ccnt];
						}
					}
					//$result[$scnt][$rcnt][$ccnt]	=	$columns[$ccnt];
				}
			}
		}
		return $result;
	}
}

//collection of ExcelFormatRow
class ExcelFormat{
	
	protected $rows;
	
	public function __construct(){
		$this->rows			=	Array();
	}
	
	public function addRow(ExcelFormatRow $row){
		$this->rows[$row->key]=	$row;
	}
	
	public function getRows(){
		return $this->rows;
	}
}

//row pattern on the excel to extract
class ExcelFormatRow{
	public $key;
	public $sheet;
	public $row;
	public $column;
	public $name;
	
	public function __construct($name,$sheet,$row,$column){
		$this->sheet	=	$sheet;
		$this->name		=	$name;
		$this->column	=	$column;
		$this->row		=	$row;
		$this->key		=	$this->name;
	}
	
	public function changeKey($newkey){
		$this->key		=	$newkey;
	}
}

//for saving the excel to somewhere
abstract class ExcelSaverBase{
	
	protected $data;			//take note that $data is in format array[sheet][row][column]
	public $excelFormat;
	
	public function setData(Array $data){
		$this->data			=	$data;
	}
	
	public function setFormat(ExcelFormat $excelFormat){
		$this->excelFormat	=	$excelFormat;
	}
	
	abstract public function save();
}
?>